﻿using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Models.Entitys;
using RndMaterialDisposalService.Models.RequestModel;
using RndMaterialDisposalService.Models.ResponseModel;
using RndMaterialDisposalService.Repository;
using RndMaterialDisposalService.Services.Interface;
using AutoMapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace RndMaterialDisposalService.Services
{
    /// <summary>
    /// 
    /// </summary>
    [NAutowired(Lifecycle.Scoped)]
    public class CapitalScrapReportFormService : ICapitalScrapReportFormService
    {
        private readonly IMapper mapper;
        private readonly AssetsManagementContext dbContext;
        private readonly WorkflowCommonDataContext dbCommonContext;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_dbContext"></param>
        /// <param name="_mapper"></param>
        /// <param name="_dbCommonContext"></param>
        public CapitalScrapReportFormService(AssetsManagementContext _dbContext, IMapper _mapper, WorkflowCommonDataContext _dbCommonContext)
        {
            dbContext = _dbContext;
            mapper = _mapper;
            dbCommonContext = _dbCommonContext;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="formData"></param>
        /// <returns></returns>
        public async Task<int> AddCapitalScrapBusinessData(CapitalScrapElectronFlowReq formData)
        {
            var repairBusiness = mapper.Map<CapitalScrapReportDto>((formData.scrapApplication));
            repairBusiness.ProcessFolio = formData.formHeader.ProcFolio!.Trim();
            repairBusiness.ProcessSubject= formData.formHeader.Subject;
            repairBusiness.ProcInstId = formData.formHeader.ProcInstId;
            var repairBusinessSubtable = mapper.Map<List<CapitalScrapReportSubtableDto>>(formData.assetDetail);
            repairBusinessSubtable.ForEach(p => p.ApplicationNumber = formData.formHeader.ProcFolio!);

            //删除已存在业务数据
            var CapitalScrapReportItem = await dbContext.CapitalScrapReport.FirstOrDefaultAsync(a => a.ProcInstId == formData.formHeader.ProcInstId);
            if (CapitalScrapReportItem != null)
            {
                dbContext.CapitalScrapReport.Remove(CapitalScrapReportItem);
            }

            var CapitalScrapReportSubtableItem = dbContext.CapitalScrapReportSubtable.Where(a => a.ApplicationNumber == formData.formHeader.ProcFolio);
            if (CapitalScrapReportSubtableItem != null)
            {
                dbContext.CapitalScrapReportSubtable.RemoveRange(CapitalScrapReportSubtableItem);
            }

            dbContext.CapitalScrapReport.Add(repairBusiness);
            dbContext.CapitalScrapReportSubtable.AddRange(repairBusinessSubtable);
            return await dbContext.SaveChangesAsync();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="scrapReport"></param>
        /// <returns></returns>
        public async Task<(List<ScrapReportDataResp>, int)> CapitalScrapReportData(ScrapReportReq scrapReport)
        {
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(scrapReport.ProcessNumber))
            {
                condition += $"AND w.ApplicationNumber LIKE '%{scrapReport.ProcessNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(scrapReport.ProcStartDateStr))
            {
                //condition += $"AND w.CreatedDate >= '{scrapReport.ProcStartDateStr}' ";
                string[] timeArr = scrapReport.ProcStartDateStr.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND w.CreatedDate >= '{timeArr[0]} 00:00:00' AND w.CreatedDate <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(scrapReport.ProcStatus))
            {
                condition += $"AND w.CurrentStatus = '{scrapReport.ProcStatus}' ";
            }
            if (!string.IsNullOrWhiteSpace(scrapReport.Department))
            {
                condition += $"AND (s.LargeSectors LIKE '%{scrapReport.Department}%' OR s.SmallSector LIKE '%{scrapReport.Department}%' OR s.DepartmentCode LIKE '%{scrapReport.Department}%') ";
            }
            if (!string.IsNullOrWhiteSpace(scrapReport.AssetNo))
            {
                condition += $"AND c.AssetNumber LIKE '%{scrapReport.AssetNo}%' ";
                //condition += $"AND c.AssetNumber LIKE '%'+@AssetNo+'%' ";
                //SqlParameter sqlPara = new SqlParameter("@AssetNo", SqlDbType.NVarChar);
                //sqlPara.Value = scrapReport.AssetNo;
                //sqlParameters.Add(sqlPara);
            }
            if (!string.IsNullOrWhiteSpace(scrapReport.ApplicationNumber))
            {
                condition += $"AND w.ApplicantEmployeeNumber = '{scrapReport.ApplicationNumber}' ";
            }
            if (!string.IsNullOrWhiteSpace(scrapReport.CompanySegment)) 
            {
                condition += $"AND s.CompanySegment LIKE '%{scrapReport.CompanySegment}%' ";
            }
            if (!string.IsNullOrWhiteSpace(scrapReport.FirstLevelAssetManager)) 
            {
                condition += $"AND s.FirstLevelAssetManager = '{scrapReport.FirstLevelAssetManager}' ";
            }

            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(scrapReport.CurrentUser))
            {
                //获取当前用户资产清退报废电子流审批流程记录,去重后联查得到ProcInstId
                //string _sql = $"SELECT ProcessFolio AS ProcInstId FROM AssetsManagement.dbo.CapitalScrapBusinessData WHERE ProcessFolio IN (SELECT DISTINCT(ApplicationNumber) FROM VW_Common.dbo.WorkflowApproveData WHERE WorkflowName = '资产清退报废电子流' AND ApproverName = '{scrapReport.CurrentUser}')";
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.CapitalScrapBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber AND WorkflowName = '资产清退报废电子流')  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '资产清退报废电子流' AND ApproverName = '{scrapReport.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql,new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ApplicationNumber).ToList();

                //当前用户工号
                string employeeNumber = scrapReport.CurrentUser.Substring(0, scrapReport.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {
                    //获取发起流程实例
                    var LaunchList = dbCommonContext.ApplicationData.Where(p=> "资产清退报废电子流".Equals(p.WorkflowName!.Trim()) && p.ApplicantEmployeeNumber== employeeNumber && p.ProcInstId != "").Select(p => p.ApplicationNumber).Distinct().ToList();
                    ApprovalHistoryProcInstIdList.AddRange(LaunchList);
                }

                string ApplicationNumber_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ApplicationNumber_STR = string.Join(',', _ProcInstIdList);
                }

                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.CapitalScrapBusinessSubtableData WHERE ApplicationNumber IN({ApplicationNumber_STR}))";
            }
            else 
            {
                SqlInsertClip = "(SELECT * FROM AssetsManagement.dbo.CapitalScrapBusinessSubtableData WHERE ApplicationNumber !='')";
            }


            string sql = @$"SELECT c.SysId,w.ApplicationNumber,w.ProcInstId,w.ApplicantEmployeeNumber,w.ApplicantName,w.CurrentHandler,w.CurrentStatus,w.CreatedDate,s.CompanySegment,s.LargeSectors,
                            s.SmallSector,s.DepartmentCode,c.AssetNumber,c.AssetDesription,c.AssetOwnerName,c.OwnerEmployeeNumber,c.QualityStatus,c.AssetCost,c.TotalDeprecition,
                            c.NetWorth,c.AssetQuality,c.PCAssetRetirementStandard,c.GetDate,s.LogisticsApproverId,s.LogisticsApprover,s.ReasonForScrapping,c.DeptName,c.DeptNumber,s.FirstLevelAssetManager
                            FROM {SqlInsertClip} c 
                            LEFT JOIN AssetsManagement.dbo.CapitalScrapBusinessData s ON c.ApplicationNumber = s.ProcessFolio 
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData w ON c.ApplicationNumber = w.ApplicationNumber AND w.WorkflowName = '资产清退报废电子流'  
                            WHERE {condition} ORDER BY CreatedDate DESC";
            var count = ExecuteSqlRaw(sql, sqlParameters);
            var list = SqlQuery<ScrapReportDataResp>(sql, sqlParameters);
            //赋值流程最早时间
            if (!string.IsNullOrWhiteSpace(scrapReport.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.CreatedDate = item.ApproveDate;
                    }
                });
            }

            list.ForEach((x) => {
                //if (!string.IsNullOrWhiteSpace(x.ApplicantName))
                //{
                //    x.ApplicantName = x.ApplicantName.Substring(x.ApplicantName.IndexOf(" ") + 1).Trim();
                //}
                x.ApplicantName = x.ApplicantEmployeeNumber.Trim() + " " + x.ApplicantName.Trim();
                x.ApplicationNumber = x.ApplicationNumber.Trim();
            });

            return (list, count);
        }


        public List<T> SqlQuery<T>(string sql, List<SqlParameter> sqlParameters) where T : class, new()
        {
            List<T> list = new List<T>();
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }

        public int ExecuteSqlRaw(string sql, List<SqlParameter> sqlParameters)
        {
            var count = 0;
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    count += 1;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return count;
        }


    }
}
